package fr.afcepf.al22.qualite.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import fr.afcepf.al22.qualite.dao.util.MyDatasource;
import fr.afcepf.al22.qualite.entity.Personne;

/**
 * @author Stagiaire
 *
 */
public class DaoPersonneImpl implements IDaoPersonne {

    /**
     * SQL_AJOUTER.
     */
    private static final  String SQL_AJOUTER  = "INSERT INTO Personne"
            + "(nom, mail, mdp, naissance) VALUES"
            + "(?,?,?,?)";
    /**
     * SQL_RECHERCHER_PAR_MAIL.
     */
    private static final  String SQL_RECHERCHER_PAR_MAIL  = "SELECT id, nom, mail, mdp, naissance FROM Personne WHERE mail = ?";
    
    /**
     * SQL_RECHERCHER_PAR_NOM.
     */
    private static final  String SQL_RECHERCHER_PAR_NOM  = "SELECT id, nom, mail, mdp, naissance FROM Personne WHERE nom = ?";
    /**
     * INDICE_NOM.
     */
    private static final  int INDICE_NOM = 1;
    /**
     * INDICE_MAIL.
     */
    private static  final int INDICE_MAIL = 2;
    /**
     * INDICE_MDP.
     */
    private  static final  int INDICE_MDP = 3;
    /**
     * INDICE_NAISSANCE.
     */
    private  static final int INDICE_NAISSANCE = 4;
    /**
     * INDICE_ID.
     */
    private  static final int INDICE_ID = 1;
    /**
     * INDICE_RECHERCHE_MAIL.
     */
    private  static final int INDICE_RECHERCHE_MAIL = 1;
    /**
     * INDICE_RECHERCHE_MAIL.
     */
    private  static final int INDICE_RECHERCHE_NOM = 1;
    @Override
    public Personne ajouter(Personne paramPers) {
        // TODO Auto-generated method stub
        DataSource ds = new MyDatasource();
        Connection connection = null;
        try {
           connection = ds.getConnection();
            PreparedStatement statement = connection.prepareStatement(SQL_AJOUTER, Statement.RETURN_GENERATED_KEYS);
            statement.setString(INDICE_NOM, paramPers.getNom());
            statement.setString(INDICE_MAIL, paramPers.getMail());
            statement.setString(INDICE_MDP, paramPers.getMdp());
            statement.setDate(INDICE_NAISSANCE, new java.sql.Date(paramPers.getNaissance().getTime()));
            int nbLigne = statement.executeUpdate();
            ResultSet rskey = statement.getGeneratedKeys();
            if (nbLigne == 1 && rskey.next()) {
                paramPers.setId(rskey.getInt(INDICE_ID));
            }
        } catch (Exception e) {
            System.out.println("SQL ERROR : " + e.getMessage());
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        return paramPers;
    }

    @Override
    public List<Personne> rechercher(String paramNom) {
        // TODO Auto-generated method stub
        Personne pers = null;
        DataSource ds = new MyDatasource();
        List <Personne> listePersonne = new  ArrayList <Personne>();
        Connection connection = null;
        try {
           connection = ds.getConnection();
            PreparedStatement statement = connection.prepareStatement(SQL_RECHERCHER_PAR_NOM);
            statement.setString(INDICE_RECHERCHE_NOM, paramNom);
            ResultSet rs = statement.executeQuery();
            while (rs.next()){
                pers = new Personne();
                pers.setId( rs.getInt("id"));
                pers.setNom( rs.getString("nom"));
                pers.setMail( rs.getString("mail"));
                pers.setMdp( rs.getString("mdp"));
                pers.setNaissance(rs.getDate("naissance"));
                listePersonne.add(pers);
            }
        } catch (Exception e) {
            System.out.println("SQL ERROR : " + e.getMessage());
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        return listePersonne;
    }

    @Override
    public Personne verifMail(String paramMail) {
        // TODO Auto-generated method stub
        Personne pers = null;
        DataSource ds = new MyDatasource();
        Connection connection = null;
        try {
           connection = ds.getConnection();
            PreparedStatement statement = connection.prepareStatement(SQL_RECHERCHER_PAR_MAIL);
            statement.setString(INDICE_RECHERCHE_MAIL, paramMail);
            ResultSet rs = statement.executeQuery();
            if (rs.next()){
                pers = new Personne();
                pers.setId( rs.getInt("id"));
                pers.setNom( rs.getString("nom"));
                pers.setMail( rs.getString("mail"));
                pers.setMdp( rs.getString("mdp"));
                pers.setNaissance(rs.getDate("naissance"));
            }
        } catch (Exception e) {
            System.out.println("SQL ERROR : " + e.getMessage());
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        return pers;
    }

}
